package com.archetypus.projectschedule.common.data.activities;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.archetypus.projectschedule.common.data.SQLServerDAOFactory;
import com.archetypus.projectschedule.common.dto.activities.Activity;
import com.archetypus.projectschedule.common.dto.activities.ActivityKey;
import com.archetypus.projectschedule.common.dto.projects.Project;
import com.archetypus.projectschedule.common.dto.projects.ProjectKey;
import com.archetypus.projectschedule.common.exceptions.ProjectSchedulerException;

public class SQLServerActivityDAO implements ActivityDAO {

	@Override
	public void delete(Activity activity) throws ProjectSchedulerException {
		String sql = "DELETE FROM activities WHERE id=?";
		Connection conn = null;
		PreparedStatement stmt = null;
		
		try{
			conn = SQLServerDAOFactory.createConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, activity.getActivityKey().getId());
			stmt.executeUpdate();
		}catch(Exception ex){
			ex.printStackTrace();
			throw new ProjectSchedulerException("Could not delete register");
		}finally{
			try{
				stmt.close();
				conn.close();
			}catch(Exception e){}
		}

	}

	@Override
	public Activity find(Activity activity) throws ProjectSchedulerException {
		String sql = "SELECT id, name, description, project FROM activities WHERE id=?";
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Activity retrievedActivity = null;
		
		try{
			conn = SQLServerDAOFactory.createConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, activity.getActivityKey().getId());
			rs = stmt.executeQuery();
			rs.next();
			retrievedActivity = new Activity();
			retrievedActivity.setDescription(rs.getString("description"));
			retrievedActivity.setName(rs.getString("name"));
			ProjectKey projectKey = new ProjectKey(rs.getInt("project"));
			retrievedActivity.setActivityKey(new ActivityKey(projectKey,rs.getInt("id")));
		}catch(Exception ex){
			ex.printStackTrace();
			throw new ProjectSchedulerException("Could not retrieve register");
		}finally{
			try{
				stmt.close();
				conn.close();
			}catch(Exception e){}
		}
		return retrievedActivity;
	}

	@Override
	public Activity insert(Activity activity) throws ProjectSchedulerException {
		String sql = "INSERT INTO activities(name,description,project) VALUES(?,?,?)";
		Connection conn = null;
		PreparedStatement stmt = null;
		
		try{
			conn = SQLServerDAOFactory.createConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, activity.getName());
			stmt.setString(2, activity.getDescription());
			stmt.setInt(3, activity.getActivityKey().getProject());
			stmt.executeUpdate();
		}catch(Exception ex){
			ex.printStackTrace();
			throw new ProjectSchedulerException("Could not insert register");
		}finally{
			try{
				stmt.close();
				conn.close();
			}catch(Exception e){}
		}
		return activity;
	}

	@Override
	public List<Activity> list() throws ProjectSchedulerException {
		String sql = "SELECT id, name, description, project FROM activities";
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		List<Activity> activitiesList = new ArrayList<Activity>();
		
		try{
			conn = SQLServerDAOFactory.createConnection();
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while(rs.next()){
				Activity a = new Activity();
				a.setDescription(rs.getString("description"));
				a.setName(rs.getString("name"));
				ProjectKey projectKey = new ProjectKey(rs.getInt("project"));
				a.setActivityKey(new ActivityKey(projectKey,rs.getInt("id")));
				activitiesList.add(a);
			}
		}catch(Exception ex){
			ex.printStackTrace();
			throw new ProjectSchedulerException("Could not retrieve register");
		}finally{
			try{
				stmt.close();
				conn.close();
			}catch(Exception e){}
		}
		return activitiesList;
	}

	@Override
	public List<Activity> listByProject(Project project) throws ProjectSchedulerException {
		String sql = "SELECT id, name, description, project FROM activities WHERE project=?";
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		List<Activity> activitiesList = new ArrayList<Activity>();
		
		try{
			conn = SQLServerDAOFactory.createConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, project.getProjectKey().getId());
			rs = stmt.executeQuery();
			while(rs.next()){
				Activity a = new Activity();
				a.setDescription(rs.getString("description"));
				a.setName(rs.getString("name"));
				ProjectKey projectKey = new ProjectKey(rs.getInt("project"));
				a.setActivityKey(new ActivityKey(projectKey,rs.getInt("id")));
				activitiesList.add(a);
			}
		}catch(Exception ex){
			ex.printStackTrace();
			throw new ProjectSchedulerException("Could not retrieve register");
		}finally{
			try{
				stmt.close();
				conn.close();
			}catch(Exception e){}
		}
		return activitiesList;
	}

	@Override
	public void update(Activity activity) throws ProjectSchedulerException {
		String sql = "UPDATE activities SET name=?, description=? WHERE id=? AND project=?";
		Connection conn = null;
		PreparedStatement stmt = null;
		try{
			conn = SQLServerDAOFactory.createConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, activity.getName());
			stmt.setString(2, activity.getDescription());
			stmt.setInt(3, activity.getActivityKey().getId());
			stmt.setInt(4, activity.getActivityKey().getProject());
			stmt.executeUpdate();
		}catch(Exception ex){
			ex.printStackTrace();
			throw new ProjectSchedulerException("Could not update register");
		}finally{
			try{
				stmt.close();
				conn.close();
			}catch(Exception e){}
		}

	}

}
